﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace admin
{
    /// <summary>
    /// kласс для работы с базой данных
    /// </summary>
    public class DataBase
    {
        static string connectionString = "server=localhost;database=sanatorium;uid=root;password=";
        MySqlConnection connection;
        static string allRooms = @"select r.Id_Room, r.Count, tr.Type
                                    from rooms AS r, TypesOfRooms AS tr
                                    where tr.Id_type = r.Id_Type";

        static string freeRooms = @"select r.Id_Room, r.Count, tr.Type
                                    from rooms AS r, TypesOfRooms AS tr
                                    where tr.Id_type = r.Id_Type and tr.Type = @roomclass ";

        static string trooms = "select * from TypesOfRooms";

        static string ourClients0 = @"select cl.Id_Client, cl.Name, cl.Patronymic, cl.Surname, rcl.CheckIn, rcl.CheckOut, rcl.Id_Room
                                    from clients AS cl, RoomsClients AS rcl
                                    where cl.Id_Client = rcl.Id_Client and rcl.Id_Room = @roomId";

        static string allClients = @"select cl.Id_Client, cl.Name, cl.Patronymic, cl.Surname, rcl.CheckIn, rcl.CheckOut, rcl.Id_Room
                                    from clients AS cl, RoomsClients AS rcl
                                    where cl.Id_Client = rcl.Id_Client";

        static string newClientQuery = @"INSERT INTO clients(Name, Patronymic, Surname)
                                           VALUES (@clName,  @clMiddleName, @clSurname);
                            INSERT INTO RoomsClients(checkIn, checkOut, Id_status, Id_Room, Id_Client)
                            VALUES (@clCheckIn, @clCheckOut, 1,  @clRoomId, (select Id_Client from clients as cl
                                    where cl.Name=@clName and cl.Surname=@clSurname and cl.Patronymic=@clMiddleName));";

        static string countClients = @"select COUNT(*) AS clCount
                                        from RoomsClients as rc
                                        where (rc.Id_room = @roomId) and 
                                        ((rc.CheckOut > @chkIn AND rc.CheckOut < @chkOut)
                                        OR (rc.CheckIn < @chkOut AND rc.CheckOut > @chkIn))";

        static string allClList = @"SELECT cl.Id_Client, cl.Name, cl.Patronymic, cl.Surname, rcl.CheckIn, rcl.CheckOut, rcl.Id_Room
                                    FROM clients AS cl LEFT JOIN RoomsClients AS rcl ON cl.Id_Client = rcl.Id_Client
                                    WHERE (rcl.Id_Room = @roomId) and
                                    (((rcl.CheckIn <= @chkin) and (rcl.CheckOut <= @chkout) and (rcl.CheckOut >= @chkin)) or 
                                    ((rcl.CheckIn >= @chkin) and (rcl.CheckOut >= @chkout) and (rcl.CheckIn <= @chkout)) or 
                                    ((rcl.CheckIn <= @chkin) and (rcl.CheckOut >= @chkout)) or
                                    ((rcl.CheckIn >= @chkin) and (rcl.CheckOut <= @chkout)));";

        static string freeRooms_ = @"select rm.Id_Room, rm.Count, tr.Type
                            from rooms rm, TypesOfRooms AS tr
                            where rm.Id_Type = tr.Id_Type and rm.Id_Room not in 
                            (
                            SELECT r.Id_Room
                            FROM rooms AS r  LEFT JOIN RoomsClients AS rcl ON r.Id_Room = rcl.Id_Room
                            WHERE 
                            (((rcl.CheckIn <= @chkin) and (rcl.CheckOut <= @chkout) and (rcl.CheckOut >= @chkin)) or 
                            ((rcl.CheckIn >= @chkin) and (rcl.CheckOut >= @chkout) and (rcl.CheckIn <= @chkout)) or 
                            ((rcl.CheckIn <= @chkin) and (rcl.CheckOut >= @chkout)) or
                            ((rcl.CheckIn >= @chkin) and (rcl.CheckOut <= @chkout)))
                            ) and rm.Id_type = (
                            select tx.Id_type
                            from TypesOfRooms AS tx
                            where tx.Type = @roomclass
                            );";

        static string removeQuery = @"delete from RoomsClients
                                        where Id_Client = @rcl;
                                        delete from ClientsSchedule
                                        where Id_Client = @rcl;
                                        delete from Clients
                                        where Id_Client = @rcl;";

        static string checkOutQuery = @"UPDATE roomsclients
                                        SET CheckOut = @chkOut
                                        WHERE Id_Client = @clId;";

        public DataBase()
        {
            connection = new MySqlConnection(connectionString);
        }

        /// <summary>
        /// Создание соединения
        /// </summary>
        /// <returns></returns>
        private MySqlConnection GetOpenConnection()
        {
            if (connection.State != System.Data.ConnectionState.Open)
                connection.Open();
            
            return connection;
        }

        /// <summary>
        /// отправка запроса
        /// </summary>
        /// <param name="query">строка с запросом</param>
        /// <returns></returns>
        private MySqlDataReader ExecuteQuery(string query)
        {
            //подготовка к выполнению запроса
            MySqlCommand command = new MySqlCommand(query, connection);
            MySqlDataReader reader;

            //установка соединнения
            if (connection.State != System.Data.ConnectionState.Open)
            {
                try
                {
                    connection.Open();
                }
                catch (SystemException ex)
                {
                    throw ex;
                }
            }

            //попытка выполнить запрос
            try
            {
                reader = command.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Ошибка базы данных:" + Environment.NewLine + ex.Message);
            }
            finally
            {
                //connection.Close();
            }

            return reader;
        }

        public void RemoveClient(Clients rclient)
        {
            MySqlCommand command = new MySqlCommand(removeQuery, GetOpenConnection());

            command.Parameters.Add("@rcl", MySqlDbType.Int32);
            command.Parameters["@rcl"].Value = rclient.id;

            try
            {
                command.ExecuteNonQuery();
            }
            catch (SystemException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }

        public void EscapeClient(Clients rclient, DateTime checkOut)
        {
            MySqlCommand command = new MySqlCommand(checkOutQuery, GetOpenConnection());

            command.Parameters.Add("@clId", MySqlDbType.Int32);
            command.Parameters["@clId"].Value = rclient.id;
            command.Parameters.Add("@chkOut", MySqlDbType.Date);
            command.Parameters["@chkOut"].Value = checkOut;

            try
            {
                command.ExecuteNonQuery();
            }
            catch (SystemException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Получение списка типов комнат
        /// </summary>
        /// <returns></returns>
        public List<String> GetRoomTypes()
        {
            List<String> list = new List<String>();
            MySqlDataReader reader = ExecuteQuery(trooms);
            try
            {
                while (reader.Read())
                {
                    list.Add(reader.GetString("Type"));
                }
            }
            finally
            {
                reader.Close();
            }
            return list;
        }

        /// <summary>
        /// Выборка не заселённых номеров
        /// </summary>
        /// <param name="checkIn">Дата въезда</param>
        /// <param name="checkOut">Дата выезда</param>
        /// <param name="roomcl">Тип номера</param>
        /// <returns>Список комнат</returns>
        public List<RoomsView> GetFreeRooms(DateTime checkIn, DateTime checkOut, string roomcl)
        {
            List<RoomsView> list = new List<RoomsView>();
            string s = freeRooms_;
            MySqlCommand command = new MySqlCommand(s, GetOpenConnection());

            command.Parameters.Add("@chkin", MySqlDbType.Date);
            command.Parameters["@chkin"].Value = checkIn;
            command.Parameters.Add("@chkout", MySqlDbType.Date);
            command.Parameters["@chkout"].Value = checkOut;
            command.Parameters.Add("@roomclass", MySqlDbType.String);
            command.Parameters["@roomclass"].Value = roomcl;

            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                list.Add(new RoomsView(Convert.ToInt32(reader.GetString("Id_Room")),//room 
                    Convert.ToInt32(reader.GetString("Count")),
                    Convert.ToString(reader.GetString("Type"))));
            }
            reader.Close();
            return list;
        }

        /// <summary>
        /// Построение полного списка комнат
        /// </summary>
        /// <param name="checkIn">Дата въезда</param>
        /// <param name="checkOut">Дата выезда</param>
        /// <returns>Список комнат</returns>
        public List<RoomsView> GetRooms(DateTime checkIn, DateTime checkOut)
        {
            List<RoomsView> list = new List<RoomsView>();
            string s = allRooms;
            MySqlCommand command = new MySqlCommand(s, GetOpenConnection());

            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                list.Add(new RoomsView(Convert.ToInt32(reader.GetString("Id_Room")),//room 
                    Convert.ToInt32(reader.GetString("Count")),
                    Convert.ToString(reader.GetString("Type"))));
            }
            reader.Close(); 
            return list;
        }

        /// <summary>
        /// Построение списка засёлённых клиентов по указанному номеру
        /// </summary>
        /// <param name="roomId">Номер комнаты</param>
        /// <returns>Список клиентов</returns>
        public List<Clients> GetClients(int roomId)
        {
            List<Clients> list = new List<Clients>();
            string s = ourClients0;
            MySqlCommand command = new MySqlCommand(s, GetOpenConnection());
            command.Parameters.Add("@roomId", MySqlDbType.Int32);
            command.Parameters["@roomId"].Value = roomId;

            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                list.Add(new Clients(reader.GetInt32("Id_Client"),
                    reader.GetString("Name"),
                    reader.GetString("Surname"),
                    reader.GetString("Patronymic"),
                    reader.GetDateTime("CheckIn"),
                    reader.GetDateTime("CheckOut"),
                    reader.GetInt32("Id_Room")));
            }
            reader.Close();
            return list;
        }

        /// <summary>
        /// Построение списка клиентов для комнаты, в определённом временном промежутке
        /// </summary>
        /// <param name="roomId">Номер комнаты</param>
        /// <param name="chkIn">Дата въезда</param>
        /// <param name="chkOut">Дата выезда</param>
        /// <returns>Список клиентов</returns>
        public List<Clients> GetClients(int roomId, DateTime chkIn, DateTime chkOut)
        {
            List<Clients> list = new List<Clients>();
            string s = allClList;
            MySqlCommand command = new MySqlCommand(s, GetOpenConnection());
            command.Parameters.Add("@roomId", MySqlDbType.Int32);
            command.Parameters["@roomId"].Value = roomId;
            command.Parameters.Add("@chkin", MySqlDbType.Date);
            command.Parameters["@chkin"].Value = chkIn;
            command.Parameters.Add("@chkout", MySqlDbType.Date);
            command.Parameters["@chkout"].Value = chkOut;

            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                list.Add(new Clients(reader.GetInt32("Id_Client"), 
                    reader.GetString("Name"),
                    reader.GetString("Surname"),
                    reader.GetString("Patronymic"),
                    reader.GetDateTime("CheckIn"),
                    reader.GetDateTime("CheckOut"),
                    reader.GetInt32("Id_Room")));
            }
            reader.Close();
            return list;
        }

        /// <summary>
        /// Получение числа свободных мест в номере
        /// </summary>
        /// <param name="room">Номер комнаты</param>
        /// <param name="chkIn">Дата въезда</param>
        /// <param name="chkOut">Дата выезда</param>
        /// <returns>кол-во свободных мест</returns>
        public int GetFrClient(int room, DateTime chkIn, DateTime chkOut)
        {
            int cnt = 0;
            string s = countClients;
            MySqlCommand command = new MySqlCommand(s, GetOpenConnection());
            command.Parameters.Add("@roomId", MySqlDbType.Int32);
            command.Parameters["@roomId"].Value = room;
            command.Parameters.Add("@chkIn", MySqlDbType.Date);
            command.Parameters["@chkIn"].Value = chkIn;
            command.Parameters.Add("@chkOut", MySqlDbType.Date);
            command.Parameters["@chkOut"].Value = chkOut;

            MySqlDataReader reader = command.ExecuteReader();
            try
            {
                if (reader.Read())
                {
                    cnt = reader.GetInt32("clCount");
                }
            }
            finally
            {
                reader.Close();
            }
            return cnt;
        }

        /// <summary>
        /// Поучение списка всех клиентов
        /// </summary>
        /// <returns>Список всех клиентов санатория</returns>
        public List<Clients> GetAllClients()
        {
            List<Clients> list = new List<Clients>();
            string s = allClients;
            MySqlCommand command = new MySqlCommand(s, GetOpenConnection());

            MySqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                list.Add(new Clients(reader.GetInt32("Id_Client"),
                    reader.GetString("Name"),
                    reader.GetString("Surname"),
                    reader.GetString("Patronymic"),
                    reader.GetDateTime("CheckIn"),
                    reader.GetDateTime("CheckOut"),
                    reader.GetInt32("Id_Room")));
            }
            reader.Close();
            return list;
        }

        /// <summary>
        /// Формирование и отправка запроса регистрации
        /// </summary>
        /// <param name="szName">Имя</param>
        /// <param name="szMiddleName">Отчество</param>
        /// <param name="szSurname">Фамилия</param>
        /// <param name="checkIn">Дата въезда</param>
        /// <param name="checkOut">Дата выезда</param>
        /// <param name="roomId">Номер комнаты</param>
        public void AddNewClient(string szName, string szMiddleName, string szSurname, DateTime checkIn,
                                DateTime checkOut, int roomId)
        {
            MySqlCommand command = new MySqlCommand(newClientQuery, GetOpenConnection());

            command.Parameters.Add("@clName", MySqlDbType.String);
            command.Parameters["@clName"].Value = szName;
            command.Parameters.Add("@clMiddleName", MySqlDbType.String);
            command.Parameters["@clMiddleName"].Value = szMiddleName;
            command.Parameters.Add("@clSurname", MySqlDbType.String);
            command.Parameters["@clSurname"].Value = szSurname;
            command.Parameters.Add("@clCheckIn", MySqlDbType.Date);
            command.Parameters["@clCheckIn"].Value = checkIn;
            command.Parameters.Add("@clCheckOut", MySqlDbType.Date);
            command.Parameters["@clCheckOut"].Value = checkOut;
            command.Parameters.Add("@clRoomId", MySqlDbType.Int32);
            command.Parameters["@clRoomId"].Value = roomId;

            try
            {
                command.ExecuteNonQuery();
            }
            catch (SystemException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
    };
}
